<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Class Mcampaign extends MY_Model
{
    public function __construct()
    {
        parent::__construct();
    }
    
    public function dbindex()
    {
        return $this->renderQrCode('5GT5FC');
    }
    
    public function getUserEcouponDB($number,$offset)
    {
        $limit = $offset ? intval($offset) : '0';
        $sql  = '';
        $sql1 = "SELECT e.img_qrcode, e.active, e.created, e.code_coupon, e.code_coupon_qrcode, e.id, m.username, m.fullname FROM ecoupon_campaign AS e LEFT JOIN member AS m ON e.user_create = m.id WHERE 1=1 ";
        
        $key = $this->getParamString('keyword');
        if(!in_array($key, array(null, '', '0')))
        {
            $sql .= " AND (e.title like '%%".$key."%%' OR ";
            $sql .= " e.code_coupon like '%%".$key."%%' OR ";
            $sql .= " e.title_ascii like '%%".$key."%%') ";
        }
        $sql .= "ORDER BY e.id DESC";
        
        $xdata = " LIMIT ".$limit.",".$number." ";
        $xs = $this->db->query($sql1.$sql.$xdata)->result_object();
        
        $count = $this->db->query($sql1.$sql)->num_rows();
        return array('data' => $xs, 'count' => $count);   
    }
    
    public function getUserActiveEcouponDB($number,$offset)
    {
        $limit = $offset ? intval($offset) : '0';
        $sql  = '';
        $sql1 = "SELECT e.active, e.created, e.code_coupon, e.id, e.user_create,e.time_use, m.username, m.fullname FROM ecoupon_campaign AS e INNER JOIN member AS m ON e.user_create = m.id  WHERE 1 = 1 AND e.active = 1 ";
        
        $key = $this->getParamString('keyword');
        if(!in_array($key, array(null, '', '0')))
        {
            $sql .= " AND (e.code_coupon like '%%".$key."%%' OR ";
            $sql .= " m.username like '%%".$key."%%') ";
        }
        $sql .= "ORDER BY e.id DESC";
        $xdata = " LIMIT ".$limit.",".$number." ";
        $xs = $this->db->query($sql1.$sql.$xdata)->result_object();
        $count = $this->db->query($sql1.$sql)->num_rows();
        return array('data' => $xs, 'count' => $count);   
    }
    
    
    public function checkCode($code_post)
    {
        $check = $this->db->query('SELECT id FROM ecoupon WHERE code_coupon = "'.$code_post.'"')->num_rows();
        if($check == 1)
        {
            $coupon = $this->_renderEcoupon(5);
            return $this->checkCode($ecoupon);
        }else{
            return $this->checkCode($ecoupon);
        }
    }
    
    public function addUserEcouponDB($id,$admin_id,$code,$user_create,$restaurant_id,$log_work_car,$log_bills,$log_customers,$log_revenue,$log_customer,$log_mobile,$log_gift)
    {
		$data = array(
			'admin_id' => $admin_id,
			'time_use' => date('Y-m-d H:i:s'),
			'active' => 1
			);
        
		$checkUser = $this->db->query('SELECT id FROM member WHERE id = '.$user_create.' AND active = 2')->num_rows();
        if($checkUser > 0)
        {
            return 201;
        }
        
        $this->db->update('ecoupon_campaign',$data,'`id` = '.$id); 
        $data_ = array ( 'created' => date('Y-m-d H:i:s'), 'restaurant_id' => $restaurant_id, 'work_car' => $log_work_car, 'bills' => $log_bills, 'customers' => $log_customers, 'revenue' => $log_revenue, 'code_voucher' => $code, 'customer' =>$log_customer, 'mobile' => $log_mobile, 'gift' => $log_gift );
        $this->db->insert('log_use_voucher',$data_);
        return 200;
    }
    
    public function getForm($coupon)
    {
		$ctent = json_decode($this->db->query('SELECT * FROM options WHERE tags = "form_email_voucher"')->row()->content);
		$ctent_  = $ctent->header;
        return array ( 'ctent' => $ctent_, 'data' => $this->db->query('SELECT ec.code_coupon, ec.code_coupon_qrcode, ec.img_qrcode,ec.user_create,ec.active,ec.created, m.fullname, m.username,m.email FROM ecoupon_campaign AS ec LEFT JOIN member AS m ON ec.user_create = m.id WHERE ec.code_coupon = "'.$coupon.'" AND ec.active = 0')->row_array() );
    }
    
    public function getDetailCoupon($id)
    {
		//die('1');
        return $this->db->query('SELECT luv.*,ec.id, ec.code_coupon, ec.active, ec.user_create, ec.created, ec.time_use, m.fullname, m.username FROM ecoupon_campaign AS ec JOIN member AS m ON ec.user_create = m.id LEFT JOIN log_use_voucher AS luv ON ec.code_coupon = luv.code_voucher WHERE ec.id = '.$id)->row_array();
    }
    public function getDetailCode($id)
    {
        return $this->db->query('SELECT lug.*, cu.id AS cuid, cu.code_user, cu.active AS active, cu.user_create AS user_create , m.fullname AS fullname, m.username AS username FROM code_user AS cu LEFT JOIN member AS m ON cu.user_create = m.id LEFT JOIN log_use_gift AS lug ON cu.code_user = lug.code_user WHERE cu.id = '.$id)->row_array();
    }
    
    
    public function selectMember($id)
    {
        $html = '';
        $sql = "SELECT id,username,fullname FROM member WHERE active = 1 ORDER BY id ASC ";
        $q = $this->db->query($sql);
        $data = $q->result_array();
        foreach($data AS $le=>$el)
        {
            if($id == $el['id'])
            {
                $selected = " selected = 'selected' ";
            }else{
                $selected = " ";
            }
            $html .= "<option value=".$el['id']." ".$selected.">".$el['username']." ---|--- ".$el['fullname']."</option>";
        }
        return $html;
    }
    
    public function checkuser($id)
    {
        $html = '';
        $sql = "SELECT fullname FROM member WHERE id = ".$id." ORDER BY id ASC ";
        $q = $this->db->query($sql);
        return $q->row_object()->fullname;
    }
    
    
    /**
     * 
     * 
     * @void Usercode
     * 
     */
    
    public function getUserCodeDB($number,$offset)
    {
        $limit = $offset ? intval($offset) : '0';
        $sql  = '';
        $sql1 = "SELECT cu.id, cu.code_user, cu.active, cu.admin_id, cu.user_id, cu.user_create, cu.created, m.username, m.fullname FROM code_user AS cu LEFT JOIN member AS m ON m.id = cu.user_create ";
        
        $key = $this->getParamString('keyword');
        if(!in_array($key, array(null, '', '0')))
        {
            $sql .= " AND cu.code_user like '%%".$key."%%' ";
        }
        $sql .= "ORDER BY cu.id DESC";
        
        $xdata = " LIMIT ".$limit.",".$number." ";
        $xs = $this->db->query($sql1.$sql.$xdata)->result_object();
        
        $count = $this->db->query($sql1.$sql)->num_rows();
        return array('data' => $xs, 'count' => $count);   
    }
    
    public function getUserActiveCodeDB($number,$offset)
    {
        $limit = $offset ? intval($offset) : '0';
        $sql  = '';
        $sql1 = "SELECT cu.id, cu.code_user, cu.active, cu.user_create, cu.created, m.username, m.fullname, m.id AS mid FROM code_user AS cu JOIN member AS m ON m.id = cu.user_create WHERE cu.active = 1 ";
        $sql .= "ORDER BY cu.id DESC";
        $xdata = " LIMIT ".$limit.",".$number." ";
        $xs = $this->db->query($sql1.$sql.$xdata)->result_object(); 

        $count = $this->db->query($sql1.$sql)->num_rows();
        return array('data' => $xs, 'count' => $count);   
    }
    
    
    public function checkCodeUser($code_post)
    {
        $check = $this->db->query('SELECT id FROM ecoupon WHERE code_coupon = "'.$code_post.'"')->num_rows();
        if($check == 1)
        {
            $coupon = $this->_renderEcoupon(5);
            return $this->checkCode($ecoupon);
        }else{
            return $this->checkCode($ecoupon);
        }
    }
    
    public function addUserCodeDB($userID,$restaurant_id,$log_work_car,$log_bills,$log_customers,$log_revenue,$log_customer,$log_mobile,$log_gift)
    {
        $data['id_user']         = $this->getParamInt('user');
        $data['code_user']       = $this->getParamInt('code_user');
        $data['id_admin']        = $userID;
        $data['created']         = date('Y-m-d H:i:s');
        
        $checkUser = $this->db->query('SELECT id FROM member WHERE id = '.$data['id_user'].' AND active = 2')->num_rows();
        if($checkUser > 0)
        {
            return 201;
        }
        
        $this->db->query("UPDATE `code_user` SET active = 1, admin_id = ".$userID." WHERE (`code_user`='".$this->getParamInt('code_user')."')");
		
		$data_ = array ( 'created' => date('Y-m-d H:i:s'), 'restaurant_id' => $restaurant_id, 'work_car' => $log_work_car, 'bills' => $log_bills, 'customers' => $log_customers, 'revenue' => $log_revenue, 'code_user' => $this->getParamInt('code_user'), 'customer' =>$log_customer, 'mobile' => $log_mobile, 'gift' => $log_gift );
		$this->db->insert('log_use_gift',$data_);

        return 200;
    }
    
    
    public function getFormCode($coupon)
    {
        return $this->db->query('SELECT cu.code_user, m.username, m.fullname,m.id AS user_id, cu.active FROM code_user AS cu JOIN member AS m ON m.id = cu.user_create WHERE cu.code_user = "'.$coupon.'" AND cu.active = 0')->row_array();
    }
	
	public function listRestaurant($id){
		$data = $this->db->query('SELECT * FROM restaurant WHERE 1 = 1')->result_object();
        $html = '<option value = "">Chọn nhà hàng</option>';
        foreach($data AS $el=>$le)
        {
            if($le->id == $id)
            {
                $selected = 'selected="selected"';
            }else{
                $selected = '';
            }
            $html .= '<option value="'.$le->id.'" '.$selected.'>'.$le->name.'</option>';
        }
		return $html;
    }
    
     public function getReportUserCode($number,$offset,$restaurant_id = 0)
    {
        $limit = $offset ? intval($offset) : '0';
        if(in_array($restaurant_id, array(null, '', 0))){
            $sql = "SELECT u.fullname,cu.code_user, cu.active, cu.created AS time_get,lg.work_car, lg.customers, lg.bills,lg.revenue, lg.customer, lg.gift, lg.created AS time_use, r.name AS restaurant FROM code_user AS cu INNER JOIN log_use_gift AS lg ON cu.code_user = lg.code_user LEFT JOIN restaurant AS r on r.id = lg.restaurant_id LEFT JOIN staff AS u ON u.id = cu.admin_id";    
        }else{
            $sql = "SELECT u.fullname, cu.code_user, cu.active, cu.created AS time_get,lg.work_car,lg.customers,lg.revenue, lg.bills, lg.customer, lg.gift, lg.created AS time_use, r.name AS restaurant FROM code_user AS cu INNER JOIN log_use_gift AS lg ON cu.code_user = lg.code_user LEFT JOIN restaurant AS r on r.id = lg.restaurant_id LEFT JOIN staff AS u ON u.id = cu.admin_id WHERE lg.restaurant_id = ".$restaurant_id;
        }
        
        $sql .= " ORDER BY lg.id DESC";
        
        $xdata = " LIMIT ".$limit.",".$number." ";
        $xs = $this->db->query($sql.$xdata)->result_object(); //echo '<pre>';die(print_r($xs));
         
        $count = $this->db->query($sql)->num_rows();
        return array('data' => $xs, 'count' => $count);
    }
    
	public function getReportUserVoucher($number,$offset,$restaurant_id = 0){
		//die('---');
		$limit = $offset ? intval($offset) : '0';
        if(in_array($restaurant_id, array(null, '', 0))){
            $sql = "SELECT u.fullname, ec.code_coupon, ec.active, ec.admin_id, ec.user_create, ec.created AS time_get,lg.revenue, lg.work_car, lg.customers, lg.bills, lg.customer, lg.gift, lg.created AS time_use, r.name AS restaurant FROM ecoupon_campaign AS ec INNER JOIN log_use_voucher AS lg ON ec.code_coupon = lg.code_voucher LEFT JOIN restaurant AS r on r.id = lg.restaurant_id LEFT JOIN staff AS u ON u.id = ec.admin_id";    
        }else{
            $sql = "SELECT u.fullname, ec.code_coupon, ec.active, ec.admin_id, ec.user_create, ec.created AS time_get,lg.revenue, lg.work_car, lg.customers, lg.bills, lg.customer, lg.gift, lg.created AS time_use, r.name AS restaurant FROM ecoupon_campaign AS ec INNER JOIN log_use_voucher AS lg ON ec.code_coupon = lg.code_voucher LEFT JOIN restaurant AS r on r.id = lg.restaurant_id LEFT JOIN staff AS u ON u.id = ec.admin_id WHERE lg.restaurant_id = ".$restaurant_id;
        }
        
        $sql .= " ORDER BY lg.id DESC";
        
        $xdata = " LIMIT ".$limit.",".$number." ";
        $xs = $this->db->query($sql.$xdata)->result_object(); //echo '<pre>';die(print_r($xs));
         
        $count = $this->db->query($sql)->num_rows();
        return array('data' => $xs, 'count' => $count);
	}
	
    public function getReportExport()
    {
        $restaurant_id = $this->getParamInt('restaurant_id');
        if(in_array($restaurant_id, array(null, '', 0))){
            $sql = "SELECT u.fullname, ec.code_coupon, ec.active, ec.admin_id, ec.user_create, ec.created AS time_get,lg.revenue, lg.work_car, lg.customers, lg.bills, lg.customer, lg.gift, lg.created AS time_use, r.name AS restaurant FROM ecoupon_campaign AS ec INNER JOIN log_use_voucher AS lg ON ec.code_coupon = lg.code_voucher LEFT JOIN restaurant AS r on r.id = lg.restaurant_id LEFT JOIN staff AS u ON u.id = ec.admin_id";    
        }else{
            $sql = "SELECT u.fullname, ec.code_coupon, ec.active, ec.admin_id, ec.user_create, ec.created AS time_get,lg.revenue, lg.work_car, lg.customers, lg.bills, lg.customer, lg.gift, lg.created AS time_use, r.name AS restaurant FROM ecoupon_campaign AS ec INNER JOIN log_use_voucher AS lg ON ec.code_coupon = lg.code_voucher LEFT JOIN restaurant AS r on r.id = lg.restaurant_id LEFT JOIN staff AS u ON u.id = ec.admin_id WHERE lg.restaurant_id = ".$restaurant_id;
        }
        
        $sql .= " ORDER BY lg.id DESC";
        $data = $this->db->query($sql)->result_object();
        return $data;
    }
	
	 public function getReportExportgiftcode()
    {
        $restaurant_id = $this->getParamInt('restaurant_id');
        
        if(in_array($restaurant_id, array(null, '', 0))){
            $sql = "SELECT u.fullname,cu.code_user, cu.active, cu.created AS time_get,lg.work_car, lg.customers,lg.revenue, lg.bills, lg.customer, lg.gift, lg.created AS time_use, r.name AS restaurant FROM code_user AS cu INNER JOIN log_use_gift AS lg ON cu.code_user = lg.code_user LEFT JOIN restaurant AS r on r.id = lg.restaurant_id LEFT JOIN staff AS u ON u.id = cu.admin_id";    
        }else{
              $sql = "SELECT u.fullname, cu.code_user, cu.active, cu.created AS time_get,lg.work_car,lg.customers,lg.revenue, lg.bills, lg.customer, lg.gift, lg.created AS time_use, r.name AS restaurant FROM code_user AS cu INNER JOIN log_use_gift AS lg ON cu.code_user = lg.code_user LEFT JOIN restaurant AS r on r.id = lg.restaurant_id LEFT JOIN staff AS u ON u.id = cu.admin_id WHERE lg.restaurant_id = ".$restaurant_id;
        }
        
        $sql .= " ORDER BY lg.id DESC";
        $data = $this->db->query($sql)->result_object();
        return $data;
    }
	
    public function dbgetrestaurant()
    {
        $sql = "SELECT id,name FROM restaurant ORDER BY name ASC";
        $data = $this->db->query($sql)->result_array();
        return $data;
    }
	
	public function EmailVoucher(){
		$data = $this->db->query('SELECT content FROM options WHERE tags = "form_email_voucher"')->row()->content;
		return json_decode($data);
	}
	
	public function EmailGift(){
		$data = $this->db->query('SELECT content FROM options WHERE tags = "form_email_gift"')->row()->content;
		return json_decode($data);
	}
	
	public function editFixEmail($header1,$content1,$header2,$content2){
		$data1 = json_encode(array ( 'header' => $header1, 'content' => $content1 )); //echo '<pre>'; die(print_r($data1));
		$data2 = json_encode(array ( 'header' => $header2, 'content' => $content2 ));
		$this->db->update('options',array ('content' => $data1),'`tags` = "form_email_voucher"');
		$this->db->update('options',array ('content' => $data2),'`tags` = "form_email_gift"'); 
		return 200;
	}
	
	public function ajaxExport1Voucher($code_voucher){
		$sql = "SELECT u.fullname, ec.code_coupon, ec.active, ec.admin_id, ec.user_create, ec.created AS time_get,lg.revenue, lg.work_car, lg.customers, lg.bills, lg.customer, lg.gift, lg.created AS time_use, r.name AS restaurant FROM ecoupon_campaign AS ec INNER JOIN log_use_voucher AS lg ON ec.code_coupon = lg.code_voucher LEFT JOIN restaurant AS r on r.id = lg.restaurant_id LEFT JOIN staff AS u ON u.id = ec.admin_id WHERE ec.code_coupon = '".$code_voucher."'";
		$data = $this->db->query($sql)->row_object();
		return $data;
	}
	public function ajaxExport1Code($code_code){
		$sql = "SELECT u.fullname, cu.code_user, cu.active, cu.created AS time_get,lg.work_car,lg.customers,lg.revenue, lg.bills, lg.customer, lg.gift, lg.created AS time_use, r.name AS restaurant FROM code_user AS cu INNER JOIN log_use_gift AS lg ON cu.code_user = lg.code_user LEFT JOIN restaurant AS r on r.id = lg.restaurant_id LEFT JOIN staff AS u ON u.id = cu.admin_id WHERE cu.code_user = ".$code_code;
		$data = $this->db->query($sql)->row_object();
		return $data;
	}
	
	////
	
	public function getUseVoucher($code){
        return  $this->db->query('SELECT ec.id, ec.code_coupon, ec.user_create,ec.active,ec.created, m.fullname, m.username,m.email FROM ecoupon_campaign AS ec LEFT JOIN member AS m ON ec.user_create = m.id WHERE ec.code_coupon = "'.$code.'" AND ec.active = 0')->row_array();
	}
	
    public function checkUseVoucher($code){
        $data = $this->db->query('SELECT ec.id, ec.code_coupon, ec.user_create,ec.active,ec.created, m.fullname, m.username,m.email FROM ecoupon_campaign AS ec LEFT JOIN member AS m ON ec.user_create = m.id WHERE ec.code_coupon = "'.$code.'" AND ec.active = 1');
        if ( $data->num_rows() == 0 ){
            return 0;
        }else{
            return 1;
        }
    }
    
}